EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'CaixaAutomatico'
GO
USE [master]
GO
ALTER DATABASE [CaixaEletronico] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [CaixaEletronico]
GO

USE Master
GO
CREATE DATABASE [CaixaEletronico]
GO
USE [CaixaEletronico]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Agencia]') AND type in (N'U'))
    DROP TABLE [dbo].[Agencia]
GO
CREATE TABLE [dbo].[Agencia] (
[CodAgencia] int identity  NOT NULL  
, [CodBanco] int  NOT NULL  
, [Numero] int  NOT NULL  
, [Nome] varchar(255)  NOT NULL  
)
GO

ALTER TABLE [dbo].[Agencia] ADD CONSTRAINT [PK_Agencia] PRIMARY KEY CLUSTERED (
[CodAgencia]
)
GO
CREATE   INDEX [IX_Agencia_CodBanco] ON [dbo].[Agencia] (
[CodBanco]
)
CREATE   INDEX [IX_Agencia_NumeroAgencia] ON [dbo].[Agencia] (
[Numero]
)
CREATE UNIQUE  INDEX [UK_Agencia_NumeroAgencia] ON [dbo].[Agencia] (
[Numero]
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Banco]') AND type in (N'U'))
    DROP TABLE [dbo].[Banco]
GO
CREATE TABLE [dbo].[Banco] (
[CodBanco] int identity  NOT NULL  
, [Numero] int  NOT NULL  
, [Nome] varchar(255)  NOT NULL  
)
GO

ALTER TABLE [dbo].[Banco] ADD CONSTRAINT [PK_Banco] PRIMARY KEY CLUSTERED (
[CodBanco]
)
GO
CREATE   INDEX [IX_Banco_NumeroBanco] ON [dbo].[Banco] (
[Numero]
)
CREATE UNIQUE  INDEX [UK_Banco_NumeroBanco] ON [dbo].[Banco] (
[Numero]
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usuario]') AND type in (N'U'))
    DROP TABLE [dbo].[Usuario]
GO
CREATE TABLE [dbo].[Usuario] (
[CodUsuario] int identity  NOT NULL  
, [Login] varchar(20)  NOT NULL  
, [Senha] varchar(32)  NOT NULL  
)
GO

ALTER TABLE [dbo].[Usuario] ADD CONSTRAINT [PK_Usuario] PRIMARY KEY CLUSTERED (
[CodUsuario]
)
GO
CREATE   INDEX [IX_Usuario_Login] ON [dbo].[Usuario] (
[Login]
)
CREATE UNIQUE  INDEX [UK_Usuario_Login] ON [dbo].[Usuario] (
[Login]
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CaixaEletronico]') AND type in (N'U'))
    DROP TABLE [dbo].[CaixaEletronico]
GO
CREATE TABLE [dbo].[CaixaEletronico] (
[CodCaixa] int identity  NOT NULL  
, [CodAgencia] int  NOT NULL  
, [Saldo] decimal(18,2)  NOT NULL  
)
GO

ALTER TABLE [dbo].[CaixaEletronico] ADD CONSTRAINT [PK_CaixaEletronico] PRIMARY KEY CLUSTERED (
[CodCaixa]
)
GO
CREATE   INDEX [IX_CaixaEletronico_CodAgencia] ON [dbo].[CaixaEletronico] (
[CodAgencia]
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cliente]') AND type in (N'U'))
    DROP TABLE [dbo].[Cliente]
GO
CREATE TABLE [dbo].[Cliente] (
[CodCliente] int identity  NOT NULL  
, [Nome] varchar(100)  NOT NULL  
, [Endereco] varchar(255)  NULL  
, [RG] varchar(20)  NULL  
, [CPF] varchar(20)  NOT NULL  
, [Idade] smallint  NULL  
)
GO

ALTER TABLE [dbo].[Cliente] ADD CONSTRAINT [PK_Cliente] PRIMARY KEY CLUSTERED (
[CodCliente]
)
GO
CREATE   INDEX [IX_Cliente_CPF] ON [dbo].[Cliente] (
[CPF]
)
CREATE   INDEX [IX_Cliente_RG] ON [dbo].[Cliente] (
[RG]
)
CREATE   INDEX [IX_Cliente_Nome] ON [dbo].[Cliente] (
[Nome]
)
CREATE   INDEX [IX_Cliente_Idade] ON [dbo].[Cliente] (
[Idade]
)
CREATE UNIQUE  INDEX [UK_Cliente_CPF] ON [dbo].[Cliente] (
[CPF]
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Conta]') AND type in (N'U'))
    DROP TABLE [dbo].[Conta]
GO
CREATE TABLE [dbo].[Conta] (
[CodConta] int identity  NOT NULL  
, [CodAgencia] int  NOT NULL  
, [CodCliente] int  NOT NULL  
, [Numero] int  NOT NULL  
, [Senha] varchar(32)  NOT NULL  
, [Saldo] decimal(18,2)  NOT NULL  
, [Ativo] bit  NULL  
)
GO

ALTER TABLE [dbo].[Conta] ADD CONSTRAINT [PK_Conta] PRIMARY KEY CLUSTERED (
[CodConta]
)
GO
CREATE   INDEX [IX_Conta_CodCliente] ON [dbo].[Conta] (
[CodCliente]
)
CREATE   INDEX [IX_Conta_CodAgencia] ON [dbo].[Conta] (
[CodAgencia]
)
CREATE UNIQUE  INDEX [UK_Conta_Numero] ON [dbo].[Conta] (
[Numero]
)
CREATE   INDEX [IX_Conta_Numero] ON [dbo].[Conta] (
[Numero]
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Movimentacao]') AND type in (N'U'))
    DROP TABLE [dbo].[Movimentacao]
GO
CREATE TABLE [dbo].[Movimentacao] (
[CodMovimentacao] int identity  NOT NULL  
, [CodConta] int  NOT NULL  
, [Tipo] int  NOT NULL  
, [Data] datetime  NOT NULL  
, [Descricao] varchar(50)  NOT NULL  
, [Valor] decimal(18,2)  NOT NULL  
, [Saldo] decimal(18,2)  NOT NULL  
)
GO

ALTER TABLE [dbo].[Movimentacao] ADD CONSTRAINT [PK_Movimentacao] PRIMARY KEY CLUSTERED (
[CodMovimentacao]
)
GO
CREATE   INDEX [IX_Movimentacao_CodConta] ON [dbo].[Movimentacao] (
[CodConta]
)
CREATE   INDEX [IX_Movimentacao_Descricao] ON [dbo].[Movimentacao] (
[Descricao]
)
CREATE   INDEX [IX_Movimentacao_Data] ON [dbo].[Movimentacao] (
[Data]
)
CREATE   INDEX [IX_Movimentacao_Tipo] ON [dbo].[Movimentacao] (
[Tipo]
)
GO

ALTER TABLE [dbo].[Agencia] WITH CHECK ADD CONSTRAINT [FK_AgenciaBanco] FOREIGN KEY (
[CodBanco]
)
REFERENCES [dbo].[Banco] (
[CodBanco]
)
ON UPDATE CASCADE
GO

GO

GO

ALTER TABLE [dbo].[CaixaEletronico] WITH CHECK ADD CONSTRAINT [FK_CaixaEletronicoAgencia] FOREIGN KEY (
[CodAgencia]
)
REFERENCES [dbo].[Agencia] (
[CodAgencia]
)
ON UPDATE CASCADE
GO

GO

ALTER TABLE [dbo].[Conta] WITH CHECK ADD CONSTRAINT [FK_ContaCliente] FOREIGN KEY (
[CodCliente]
)
REFERENCES [dbo].[Cliente] (
[CodCliente]
)
ON UPDATE CASCADE
ALTER TABLE [dbo].[Conta] WITH CHECK ADD CONSTRAINT [FK_ContaAgencia] FOREIGN KEY (
[CodAgencia]
)
REFERENCES [dbo].[Agencia] (
[CodAgencia]
)
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[Movimentacao] WITH CHECK ADD CONSTRAINT [FK_MovimentacaoConta] FOREIGN KEY (
[CodConta]
)
REFERENCES [dbo].[Conta] (
[CodConta]
)
ON UPDATE CASCADE
GO

